package com.example.excel;

import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Component;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/***
 * @class_name ImportExceUtil
 * @description TODO: 导入工具类
 * @author 李兵
 * @date 2019/6/6 16:39
 * @version V1.0
 */
@Component
public class ImportExceUtil {


    HashMap<String, String> letterMap = new HashMap<String, String>() {{
        put("1", "A");
        put("2", "B");
        put("3", "C");
        put("4", "D");
        put("5", "E");
        put("6", "F");
        put("7", "G");
        put("8", "H");
        put("9", "I");
        put("10", "J");
        put("11", "Q");
        put("12", "M");
        put("13", "L");
        put("14", "N");
        put("15", "O");
        put("16", "P");
        put("17", "Q");
        put("18", "R");
        put("19", "S");
        put("20", "T");
        put("21", "U");
        put("22", "V");
        put("23", "W");
        put("24", "X");
        put("25", "Y");
        put("26", "Z");
    }};

    /**
     * 去掉字符串右边的空格
     *
     * @param str 要处理的字符串
     * @return 处理后的字符串
     */
    private static String rightTrim(String str) {
        if (str == null) {
            return "";
        }
        int length = str.length();
        for (int i = length - 1; i >= 0; i--) {
            if (str.charAt(i) != 0x20) {
                break;
            }
            length--;
        }
        return str.substring(0, length);
    }

    /**
     * @description TODO: 单元格数据值
     * @author 李兵
     * @date 2019/6/6 18:22
     * @Param [cell, value]
     * @Return java.lang.String
     **/
    private static String getCellValue(Cell cell, String value) {
        if (cell != null) {
            // 注意：一定要设成这个，否则可能会出现乱码
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    // 导入时如果为公式生成的数据则无值
                    if (!cell.getStringCellValue().equals("")) {
                        value = cell.getStringCellValue();
                    } else {
                        value = cell.getNumericCellValue() + "";
                    }
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                    break;
                default:
                    value = "";
            }
        }
        return value.trim();
    }

    /**
     * @description TODO: 导入Excel 平铺式
     * @author 李兵
     * @date 2019/6/7 15:39
     * @Param [excelfile：读取的Excel文件, inputStream：接口传递的文件, getRow：读取的开始行号0开始, getColumn：读取列数0开始]
     * @Return java.util.List
     **/
    public List impExcelFileByFlat(File excelfile, InputStream inputStream, int getStartRow, int getStartColumn) {
        try {

            Workbook workbook = getWorkbook(excelfile, inputStream);
            int numberOfSheets = workbook.getNumberOfSheets();
            List list = new ArrayList(numberOfSheets);
            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheetAt = workbook.getSheetAt(i);
                List sheetDataByFlat = getSheetDataByFlat(sheetAt, getStartRow, getStartColumn);
                list.add(i, sheetDataByFlat);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * @description TODO: 导入Excel 固定格式如行多少，列多少
     * @author 李兵
     * @date 2019/6/6 18:36
     * @Param [excelfile, getRow, getColumn]
     * @Return java.util.List
     **/
    public List impExcelFileByFixedFormat(File excelfile, InputStream inputStream, int getStartRow, int getStartColumn) {
        try {
            Workbook workbook = getWorkbook(excelfile, inputStream);
            int numberOfSheets = workbook.getNumberOfSheets();
            List list = new ArrayList(numberOfSheets);
            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheetAt = workbook.getSheetAt(i);
                Map sheetDataByFixedFormat = getSheetDataByFixedFormat(sheetAt, getStartRow, getStartColumn);
                list.add(i, sheetDataByFixedFormat);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    private Workbook getWorkbook(File excelfile, InputStream inputStream) throws Exception {
        BufferedInputStream bis = null;
        Workbook wookbook = null;
        try {
            if (ObjectUtils.allNotNull(excelfile)) {
                bis = new BufferedInputStream(new FileInputStream(excelfile));
            } else {
                bis = new BufferedInputStream(inputStream);
            }
            //打开Workbook
            wookbook = WorkbookFactory.create(bis);

            return wookbook;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wookbook;
    }

    /**
     * @description TODO: 平铺式数据读取
     * @author 李兵
     * @date 2019/6/6 18:37
     * @Param [sheetAt, getRow, getColumn]
     * @Return java.util.List
     **/
    private List getSheetDataByFlat(Sheet sheetAt, int getRow, int getColumn) {
        //获取总列数
        int columnSize = 0;
        //单元格
        Cell cell = null;
        StringBuilder stringBuilder = new StringBuilder();
        //获取总行数据
        int lastRowNum = sheetAt.getLastRowNum();
        List<String[]> result = new ArrayList<String[]>(lastRowNum);
        for (int rowIndex = getRow; rowIndex <= lastRowNum; rowIndex++) {
            Row row = sheetAt.getRow(rowIndex);
            if (row == null) {
                continue;
            }
            //获取最后一列列号
            columnSize = row.getLastCellNum();
            stringBuilder.delete(0, stringBuilder.length());
            for (int columnIndex = getColumn; columnIndex < columnSize; columnIndex++) {
                cell = row.getCell(columnIndex);
                if (cell != null) {
                    stringBuilder.append(getCellValue(cell, "")).append(",");
                }
            }
            result.add(stringBuilder.toString().split(","));
        }
        return result;
    }

    /**
     * @description TODO:  获取固定式数据
     * @author 李兵
     * @date 2019/6/7 17:45
     * @Param [sheetAt, c：列开始号, r：行开始号, re：行结束号]
     * @Return java.util.List
     **/
    private Map getSheetDataByFixedFormat(Sheet sheetAt, int getStartRow, int getStartColumn) {

        //获取最后一行的行号，相当于有效行的总和
        int lastRowNum = sheetAt.getLastRowNum() + 1;
        //获取最后一列的列数，相当于有效列数和,但是要提醒下当前sheet也的所有列和开始的行号要一致，否者多出的列将不被计算。
        int columnSize = sheetAt.getRow(getStartRow).getLastCellNum();
        Map<String, String> map = new HashMap<String, String>();
        for (int i = getStartColumn; i <= columnSize; i++) {
            putValueToMap(letterMap.get(String.valueOf(i)), i, getStartRow, lastRowNum, map);
        }
        for (String key : map.keySet()) {
            String value = map.get(key);
            String[] split = value.split(",");
            //原始map对象值替换减少内存开销。
            map.replace(key, getStringValue(sheetAt, Integer.parseInt(split[1]), Integer.parseInt(split[0])));
        }
        return map;
    }

    private String getStringValue(Sheet sheet, int rowNumber, int cellNumber) {
        Row row = sheet.getRow(rowNumber - 1);
        if (ObjectUtils.anyNotNull(row)) {
            return getCellValue(row.getCell(cellNumber - 1), "");
        }
        return "";
    }

    /**
     * @description TODO: 固定单元格数据读取。
     * @author 李兵
     * @date 2019/6/7 17:43
     * @Param [str:列标识名称, c：列开始号, r：行开始号, re：行结束号, map]
     * @Return void
     **/
    private void putValueToMap(String str, int c, int r, int re, Map<String, String> map) {
        for (int k = r; k <= re; k++) {
            map.put(str + k, c + "," + k);
        }
    }
}
